Homework 2 - ER Diagrams
Project Overview
Using entity-relation diagrams (ERD), a plan can be laid out, detailing entities, their attributes, and their relationships to one another. Entities are essentially the real world objects that are being described/identified by attributes, for example, a gas heater (entity) has a serial number (attribute). A relationship that gas heater may have is to the AC/heating system, or to the water heater. This project provides real world examples of entity sets with varying relationships as practice to produce ERDs using both Chen and Crows foot diagram notations. Chen diagrams provide an explicit description of relationships while Crows foot diagrams are minimalistic, still providing the same information, just in different ways.
Real Estate Listing System
Imagine a real estate agency that wants to track properties, agents, and clients. Each property has a property ID, address, and listing price. Agents have an agent ID, name, and contact information. Clients have a client ID, name, and budget. An agent can handle multiple properties, but each property is listed by a single agent. Clients can be interested in multiple properties, and each property can have multiple interested clients.
Conceptual design in Chen Notation
Below is an entity-relation diagram in Chen notation for a real estate listing system. The diagram explores the relationships between agents, properties, and clients.
Conceptual design in Crows foot notation
Below is an entity relation diagram for the same real estate listing system as above, however this diagram utilizes Crow’s foot notation, providing simplified viewing experience.
erDiagram
PROPERTY {
int pID
int Price
string Address
}
AGENT {
int aID
int Phone
string name
}
CLIENT {
int cID
int Budget
string Name
}
PROPERTY }o--|| AGENT : has
PROPERTY }|--o{ CLIENT : has
Design Caveats
Assumptions: * Clients and agents are only related through a property * A client must be interested in at least one property * A property can have zero interested clients
Relation sets
The following are the initial relation sets for the system:
- Agent(agent_ID, name, contact_information)
- Property(property_ID, address, listing_price)
- Client(client_ID, name, budget)
Restaurant Reservation System
Envision a restaurant that wants to manage reservations, customers, and tables. Each reservation has a reservation ID, date, and time. Customers have a customer ID, name, and contact number. Tables have a table number, seating capacity, and location. A customer can make multiple reservations, and each reservation is for a single table. Each table can have multiple reservations over time, but each reservation is associated with only one customer.
Conceptual design in Chen Notation
Conceptual design in Crows foot notation
erDiagram
CUSTOMER ||--|{ RESERVATION : has
CUSTOMER{
int custID
int Phone
string Name
}
RESERVATION }o--|| TABLE : has
RESERVATION{
int resID
string Date
string Time
}
TABLE{
int Table_Num
int Capacity
string Location
}
Design Caveats
- Tables can have zero or one reservation at a time, with many over the course of a day
- While tables may have a certain capacity, customers may request more seating than the capacity
Relation sets
The following are the initial relation sets for the system:
- Reservation( reservation_ID, date, time )
- Customer( customer_ID, name, contact_number )
- Table( table_number, seating_capacity, location )
Sports Tournament Management System
Consider a sports tournament that wants to manage teams, matches, and referees. Each team has a team ID, name, and coach. Matches have a match ID, date, and location. Referees have a referee ID, name, and certification level. A team can participate in multiple matches, and each match involves two teams. A referee can officiate multiple matches, but each match is officiated by a single referee.
Conceptual design in Chen Notation
Conceptual design in Crows foot notation
erDiagram
MATCH{
int mID
string Date
string Location
}
TEAM }|--|{ MATCH : has
TEAM{
int tID
string Name
string Coach
}
REFEREE ||--o{ MATCH : has
REFEREE{
int rID
int Cert_Level
string Name
}
Design Caveats
- Although the Crow’s foot diagram denotes matches as having 1 to many teams, there are only 2 teams for each match
- Every team must play in at least one match to be included in the system
Relation sets
The following are the initial relation sets for the system:
- Teams( team_ID, name, coach )
- Match( match_ID, date, location )
- Referee( referee_ID, name, certification_level )
Reflection
- What do you find most difficult about the assignment?
- Finding where the syntax errors are. I’m used to writing code in a text editor that has a checkstyle which will highlight syntax errors, or in a Python editor, which will spit out line numbers for errors.
- What do you find easiest about the assignment?
- Learning the notation for Chen/Crow’s foot. Both are pretty intuitive, although Chen notation is highly repetitive, and while I didn’t make any errors writing the code for it on this assignment, I can tell that on larger sets of entitities, it could become a hassle and lead to minor typos that will make me want to pull my hair out when trying to find them.
- What did you learn that will make the next assignment easier?
- Well, I am unsure what the next assignment entails, however, having learned the design and implementation of ERDs, I assume this will help me organize and relate datasets for a larger project.